﻿using System.Text.RegularExpressions;
using SqlSugar;

namespace D.UtilSqlsugarOrm
{
    /// <summary>
    /// 泛型仓储，实现泛型仓储接口
    /// </summary>
    public class BaseRepository<T> : SimpleClient<T>, IBaseRepository<T> where T : class, new()
    {
        private readonly ISqlSugarClient sqlSugarClient;
        public BaseRepository(ISqlSugarClient db)
        {
            sqlSugarClient = db;
        }
        #region 新增

        /// <summary>
        /// 单个新增
        /// </summary>
        /// <param name="t"></param>
        /// <returns></returns>
        public async Task<int> AddAsync(T t)
        {
            return await sqlSugarClient.Insertable(t).ExecuteCommandAsync();
        }

        /// <summary>
        /// 批量新增
        /// </summary>
        /// <param name="ts"></param>
        /// <returns></returns>
        public async Task<int> AddListAsync(List<T> ts)
        {
            if (ts.Count <= 500)
            {
                return await sqlSugarClient.Insertable(ts).UseParameter().ExecuteCommandAsync();
            }
            else
            {
                return await sqlSugarClient.Fastest<T>().BulkCopyAsync(ts);
            }
        }

        /// <summary>
        /// 新增或更新
        /// </summary>
        /// <param name="ts">数据集合</param>
        /// <returns>返回新增数量,返回更新数量</returns>
        public async Task<Tuple<int, int>> InsertOrUpdateAsync(List<T> ts)
        {
            var x = await sqlSugarClient.Storageable(ts).ToStorageAsync(); //将数据进行分组 
            var insertNum = await x.AsInsertable.ExecuteCommandAsync(); //执行插入 （可以换成雪花ID和自增）
            var updateNum = await x.AsUpdateable.ExecuteCommandAsync(); //执行更新　
            return new Tuple<int, int>(insertNum, updateNum);
        }
        #endregion

        #region 删除

        /// <summary>
        /// 普通删除
        /// </summary>
        /// <param name="t"></param>
        /// <returns></returns>
        public async Task<int> DeleteAsync(T t)
        {
            return await sqlSugarClient.Deleteable(t).ExecuteCommandAsync();
        }

        /// <summary>
        /// 批量删除
        /// </summary>
        /// <param name="ts"></param>
        /// <returns></returns>
        public async Task<int> DeleteAsync(List<T> ts)
        {
            return await sqlSugarClient.Deleteable(ts).ExecuteCommandAsync();
        }

        /// <summary>
        /// 执行SQL删除
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <returns></returns>
        public async Task<int> DeleteAsync(string sql)
        {
            if (HasInjectionData(sql))
            {
                throw new Exception("检测出SQL注入的恶意数据");
            }
            return await sqlSugarClient.Ado.ExecuteCommandAsync(sql);
        }

        /// <summary>
        /// 根据条件删除
        /// </summary>
        /// <param name="where">表达式条件</param>
        /// <returns></returns>
        public async Task<int> DeleteAsync(System.Linq.Expressions.Expression<Func<T, bool>> where)
        {
            return await sqlSugarClient.Deleteable<T>().Where(where).ExecuteCommandAsync();
        }

        #endregion

        #region 修改

        /// <summary>
        /// 单个修改
        /// </summary>
        /// <param name="t"></param>
        /// <returns></returns>
        public async Task<int> UpdateAsync(T t)
        {
            return await sqlSugarClient.Updateable(t).ExecuteCommandAsync();
        }

        /// <summary>
        /// 批量修改
        /// </summary>
        /// <param name="ts"></param>
        /// <returns></returns>
        public async Task<int> UpdateAsync(List<T> ts)
        {
            return await sqlSugarClient.Updateable(ts).ExecuteCommandAsync();
        }

        /// <summary>
        /// 根据条件修改
        /// </summary>
        /// <param name="columns">修改列</param>
        /// <param name="where">条件</param>
        /// <returns></returns>
        public async Task<int> UpdateAsync(System.Linq.Expressions.Expression<Func<T, T>> columns, System.Linq.Expressions.Expression<Func<T, bool>> where)
        {
            return await sqlSugarClient.Updateable<T>().SetColumns(columns).Where(where).ExecuteCommandAsync();
        }

        #endregion

        #region 查询

        /// <summary>
        /// 单个查询
        /// </summary>
        /// <param name="where"></param>
        /// <returns></returns>
        public async Task<T> GetModelAsync(System.Linq.Expressions.Expression<Func<T, bool>> where)
        {
            return await sqlSugarClient.Queryable<T>().WhereIF(where != null, where).FirstAsync();
        }

        /// <summary>
        /// 获取所有列表
        /// </summary>
        /// <returns></returns>
        public async Task<List<T>> GetListAsync()
        {
            return await sqlSugarClient.Queryable<T>().ToListAsync();
        }

        /// <summary>
        /// 获取所有列表
        /// </summary>
        /// <param name="where"></param>
        /// <param name="order"></param>
        /// <returns></returns>
        public async Task<List<T>> GetListAsync(System.Linq.Expressions.Expression<Func<T, bool>> where = null, System.Linq.Expressions.Expression<Func<T, object>> order = null)
        {
            return await sqlSugarClient.Queryable<T>().WhereIF(where != null, where).OrderByIF(order != null, order).ToListAsync();
        }

        /// <summary>
        /// 获取多少个列表
        /// </summary>
        /// <param name="where"></param>
        /// <param name="tack"></param>
        /// <param name="order"></param>
        /// <returns></returns>
        public async Task<List<T>> GetListAsync(System.Linq.Expressions.Expression<Func<T, bool>> where, int tack, System.Linq.Expressions.Expression<Func<T, object>> order = null)
        {
            return await sqlSugarClient.Queryable<T>().WhereIF(where != null, where).OrderByIF(order != null, order).Take(tack).ToListAsync();
        }

        /// <summary>
        /// 排序获取单个对象
        /// </summary>
        /// <param name="where"></param>
        /// <param name="order"></param>
        /// <returns></returns>
        public async Task<T> GetMaxModelAsync(System.Linq.Expressions.Expression<Func<T, bool>> where, System.Linq.Expressions.Expression<Func<T, object>> order)
        {
            return await sqlSugarClient.Queryable<T>().WhereIF(where != null, where).OrderBy(order).FirstAsync();
        }

        /// <summary>
        /// 分页查询
        /// </summary>
        /// <param name="pageSize">每页显示多少条</param>
        /// <param name="current">当前第几页</param>
        /// <returns></returns>
        public async Task<PageModel<T>> GetPagesAsync(int pageSize, int current)
        {
            RefAsync<int> totalCount = 0;//REF和OUT不支持异步,想要真的异步这是最优解
            var list = await sqlSugarClient.Queryable<T>().ToPageListAsync(current, pageSize, totalCount);
            return new PageModel<T>() { Data = list, TotalCount = totalCount, TotalPage = (int)Math.Ceiling(totalCount / (double)pageSize) };
        }

        /// <summary>
        /// 分页查询 加入条件
        /// </summary>
        /// <param name="pageSize">每页显示多少条</param>
        /// <param name="current">当前第几页</param>
        /// <param name="where"></param>
        /// <param name="order"></param>
        /// <returns></returns>
        public async Task<PageModel<T>> GetPagesAsync(int pageSize, int current, System.Linq.Expressions.Expression<Func<T, bool>> where, System.Linq.Expressions.Expression<Func<T, object>> order = null)
        {
            RefAsync<int> totalCount = 0;//REF和OUT不支持异步,想要真的异步这是最优解
            var list = await sqlSugarClient.Queryable<T>().WhereIF(where!=null,where).OrderByIF(order != null, order).ToPageListAsync(current, pageSize, totalCount);
            return new PageModel<T>() { Data = list, TotalCount = totalCount, TotalPage = (int)Math.Ceiling(totalCount / (double)pageSize) };
        }


        /// <summary>
        /// 分页查询 加入条件 和 排序
        /// </summary>
        /// <param name="pageSize">每页显示多少条</param>
        /// <param name="current">当前第几页</param>
        /// <param name="where"></param>
        /// <param name="order"></param>
        /// <returns></returns>
        public async Task<PageModel<T>> GetOrderPagesAsync(int pageSize, int current, System.Linq.Expressions.Expression<Func<T, bool>> where, System.Linq.Expressions.Expression<Func<T, object>> order = null,bool OrderByAsc=true)
        {
            RefAsync<int> totalCount = 0;//REF和OUT不支持异步,想要真的异步这是最优解
            OrderByType type = OrderByType.Asc;
            if(!OrderByAsc)
                type = OrderByType.Desc;
            var list = await sqlSugarClient.Queryable<T>().WhereIF(where != null, where).OrderByIF(order != null, order, type).ToPageListAsync(current, pageSize, totalCount);
            return new PageModel<T>() { Data = list, TotalCount = totalCount, TotalPage = (int)Math.Ceiling(totalCount / (double)pageSize) };
        }
        /// <summary>
        /// 判断是否存在
        /// </summary>
        /// <param name="where"></param>
        /// <returns></returns>
        public async Task<bool> ExistsAsync(System.Linq.Expressions.Expression<Func<T, bool>> where)
        {
            return await sqlSugarClient.Queryable<T>().WhereIF(where != null, where).AnyAsync();
        }

        /// <summary>
        /// 获取平均值
        /// </summary>
        /// <param name="where"></param>
        /// <param name="sum"></param>
        /// <returns></returns>
        public async Task<decimal> GetSumAsync(System.Linq.Expressions.Expression<Func<T, bool>> where, System.Linq.Expressions.Expression<Func<T, decimal>> sum)
        {
            return await sqlSugarClient.Queryable<T>().WhereIF(where != null, where).SumAsync(sum);
        }

        /// <summary>
        /// 获取总数量
        /// </summary>
        /// <param name="where"></param>
        /// <returns></returns>
        public async Task<long> GetTotalCountAsync(System.Linq.Expressions.Expression<Func<T, bool>> where)
        {
            return await sqlSugarClient.Queryable<T>().WhereIF(where != null, where).CountAsync();
        }

        #endregion

        #region SQL注入
        /// <summary>
        /// 验证是否存在注入代码(条件语句）
        /// </summary>
        /// <param name="inputData"></param>
        public virtual bool HasInjectionData(string inputData)
        {
            if (string.IsNullOrEmpty(inputData))
                return false;

            //里面定义恶意字符集合
            //验证inputData是否包含恶意集合
            if (Regex.IsMatch(inputData.ToLower(), GetRegexString()))
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 获取正则表达式
        /// </summary>
        /// <returns></returns>
        private static string GetRegexString()
        {
            //构造SQL的注入关键字符
            string[] strBadChar =
            {
            "select\\s",
            "from\\s",
            "insert\\s",
            "delete\\s",
            "update\\s",
            "drop\\s",
            "truncate\\s",
            "exec\\s",
            "count\\(",
            "declare\\s",
            "asc\\(",
            "mid\\(",
            //"char\\(",
            "net user",
            "xp_cmdshell",
            "/add\\s",
            "exec master.dbo.xp_cmdshell",
            "net localgroup administrators"
        };

            //构造正则表达式
            string str_Regex = ".*(";
            for (int i = 0; i < strBadChar.Length - 1; i++)
            {
                str_Regex += strBadChar[i] + "|";
            }
            str_Regex += strBadChar[^1] + ").*";

            return str_Regex;
        }
        #endregion

        public void Dispose()
        {
        }
    }
}
